--exec sp_search_packing_slip '0',0,'0','0',0,0,'2010',1,null, null,0
-- exec sp_search_packing_slip '0',0,'4500869143','ALL',0,0,'2008',1,'09/01/2008', '09/01/2008', 0

ALTER PROC sp_search_packing_slip
	@sales_order_no		varchar (50),
	@customer_id		int,
	@customer_po_no		varchar (50),
	@status			varchar (20),
	@location_id		int,
	@part_number_id		int,
	@year			varchar(15),
	@isSample		int,
	@SCHEDULED_BEFORE VARCHAR(30), --THIS PARAM WILL BE USED FOR EXTERNAL USER TO GENERATE PACKING SLIPS
	@SHIPPED_AFTER VARCHAR(30), --THIS PARAM WILL BE USED FOR EXTERNAL USER TO VIEW SHIPPED PACKING SLIPS
	@IS_LINEITEM_WISE 	INT -- 0=true, 1=false
	
AS
BEGIN
	declare @SQLString NVARCHAR(4000)
	declare @ParamDefinition NVARCHAR(500)
	declare @results Table ( CustomerShipping_Id int, salesOrder_Id int, Label varchar(50), SalesOrder_No varchar(50),
	C_Name varchar(65), SO_Customer_PO_number varchar(50), CST_Status varchar(20), Inv_No varchar(50), location varchar(100), CST_IS_Drop_Shipment int, MAX_SCHEDULE_DATE DATETIME, PS_ISSUE_DATE DATETIME, COOL_LINE_ITEM_NO INT, PART_NUMBER VARCHAR(50), MRKT_PART_NUMBER VARCHAR(50), COOL_QTY INT, PULLED_QTY INT)
	
	declare @final_results Table ( CustomerShipping_Id int, salesOrder_Id int, Label varchar(50), SalesOrder_No varchar(50),
	C_Name varchar(65), SO_Customer_PO_number varchar(50), CST_Status varchar(20), Inv_No varchar(50), location varchar(100), CST_IS_Drop_Shipment int, MAX_SCHEDULE_DATE DATETIME, PS_ISSUE_DATE DATETIME, COOL_LINE_ITEM_NO INT, PART_NUMBER VARCHAR(50), MRKT_PART_NUMBER VARCHAR(50), COOL_QTY INT, PULLED_QTY INT, IS_READY_FOR_CONFIRM INT DEFAULT 1 )
	
	CREATE TABLE #PS_LINEITEMS_TABLE ( CustomerShipping_Id int, salesOrder_Id int, Label varchar(50), 
						SalesOrder_No varchar(50), C_Name varchar(50), SO_Customer_PO_number varchar(50), 
						CST_Status varchar(20), Inv_No varchar(50), location varchar(100), 
						CST_IS_Drop_Shipment int, MAX_SCHEDULE_DATE DATETIME, PS_ISSUE_DATE DATETIME, 
						COOL_LINE_ITEM_NO INT, PART_NUMBER VARCHAR(50), MRKT_PART_NUMBER VARCHAR(50), COOL_QTY INT, PULLED_QTY INT)

	IF @status IS NOT NULL AND  LTRIM ( RTRIM ( @status ) ) = 'OPEN'
	BEGIN
		--SET @SQLString = N'declare psCursor CURSOR for SELECT distinct SO.SalesOrder_Id, CustomerShipping_Id=-1, label=''null'', SalesOrder_No, C_Name, SO_Customer_PO_Number, CST_Status=''OPEN'', Inv_No='''', CST_IS_Drop_Shipment = -1, location = ''null'', NULL AS PS_ISSUE_DATE, COOL.COOL_LINE_ITEM_NO, P.PART_NUMBER, COOL.COOL_QTY, ISNULL(xyz.total,0) AS PULLED_QTY, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE FROM Sales_Order SO join Customer_Open_Order_LineItem COOL on SO.SalesOrder_ID = COOL.SalesOrder_ID join Customer C on SO.Customer_ID = C.Customer_ID JOIN PRODUCT P ON P.PRODUCT_ID = COOL.PRODUCT_ID left outer join (select SUM(CSLIT_Qty_Shipped) as total,LineItem_ID FROM Customer_Shipping_Line_Item_Table Where CSLIT_Status !=''CANCELLED'' group by LineItem_ID) xyz on COOL.LineItem_ID = xyz.LineItem_ID where  COOL.COOL_Status = ''APPROVED'' and ( ( xyz.total is null ) or ( xyz.total < COOL.COOL_Qty ) )'
		SET @SQLString = N'INSERT INTO #PS_LINEITEMS_TABLE(salesOrder_Id, CustomerShipping_Id,  Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, CST_IS_Drop_Shipment,
							location, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY, MAX_SCHEDULE_DATE)
						 SELECT distinct SO.SalesOrder_Id, CustomerShipping_Id=-1, label=''null'', 
							SalesOrder_No, C_Name, SO_Customer_PO_Number, 
							CST_Status=''OPEN'', Inv_No='''', CST_IS_Drop_Shipment = -1,
							location = ''null'', NULL AS PS_ISSUE_DATE, 
							COOL.COOL_LINE_ITEM_NO, P.PART_NUMBER, P.P_Marketing_Part_Number, COOL.COOL_QTY, ISNULL(xyz.total,0) AS PULLED_QTY,ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE FROM Sales_Order SO join Customer_Open_Order_LineItem COOL on SO.SalesOrder_ID = COOL.SalesOrder_ID join Customer C on SO.Customer_ID = C.Customer_ID JOIN PRODUCT P ON P.PRODUCT_ID = COOL.PRODUCT_ID left outer join (select SUM(CSLIT_Qty_Shipped) as total,LineItem_ID FROM Customer_Shipping_Line_Item_Table Where CSLIT_Status !=''CANCELLED'' group by LineItem_ID) xyz on COOL.LineItem_ID = xyz.LineItem_ID where  COOL.COOL_Status = ''APPROVED'' and ( ( xyz.total is null ) or ( xyz.total < COOL.COOL_Qty ) )'
		SET @ParamDefinition = N'@Prm_Sales_Order_No varchar(50), @Prm_Customer_PO varchar(50), @Prm_Customer_ID int, @Prm_Product_ID INT, @Prm_Year varchar(15), @Prm_isSample int, @PRM_SCHEDULED_BEFORE VARCHAR(30)'

		IF @sales_order_no IS NOT NULL AND LTRIM ( RTRIM (@sales_order_no ) ) <> '' AND LTRIM ( RTRIM (@sales_order_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SalesOrder_No = @Prm_Sales_Order_No'
		END
		IF @customer_po_no IS NOT NULL AND LTRIM ( RTRIM (@customer_po_no ) ) <> '' AND LTRIM ( RTRIM (@customer_po_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SO_Customer_PO_Number = @Prm_Customer_PO'
		END
		IF @part_number_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND P.Product_ID = @Prm_Product_ID'
		END
		IF @customer_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND C.Customer_ID = @Prm_Customer_ID'
		END
		/*IF @year IS NOT NULL AND LTRIM ( RTRIM (@year ) ) <> ''
		BEGIN
			SET @SQLString = @SQLString + ' AND YEAR( Sales_Order.SO_Booking_Date ) = @Prm_Year'
		END*/
		IF ( @SCHEDULED_BEFORE IS NOT NULL AND LEN(LTRIM(RTRIM(@SCHEDULED_BEFORE))) > 0 )
		 BEGIN
			SET @SQLString = @SQLString + ' AND ISNULL(COOL_REVISED_DATE, COOL_SCHEDULE_DATE) < CAST(@PRM_SCHEDULED_BEFORE AS DATETIME)'
		 END
		SET @SQLString = @SQLString + ' AND SO_Is_Sample = @Prm_isSample' 
		SET @SQLString = @SQLString + ' ORDER BY SalesOrder_No'
	
		execute sp_executesql @SQLString, @ParamDefinition, @Prm_Sales_Order_No = @sales_order_no, @Prm_Customer_PO = @customer_po_no, @Prm_Customer_ID = @customer_id, @Prm_Product_ID = @part_number_id,@Prm_Year = @year, @Prm_isSample = @isSample, @PRM_SCHEDULED_BEFORE = @SCHEDULED_BEFORE

	END
	ELSE IF ( @status  IS NOT  NULL AND ( @status = 'ISSUED' OR  @status = 'SHIPPED' OR  @status = 'CANCELLED' ) ) 
	BEGIN
		--set @SQLString = N'declare psCursor CURSOR for SELECT SO.SalesOrder_ID,CST.CustomerShipping_Id,Label,SalesOrder_No,C_Name, SO_Customer_PO_Number,CST_Status,Inv_No,CST_IS_Drop_Shipment, isNull( (select top 1 L_Vendor_Name+''/''+a.City from Location l, Vendor v, Address a where l.Address_ID = CST.CST_Location_Address_Id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID ), (select top 1 L_Vendor_Name+''/''+a.City from Location l, Vendor v, Address a where l.Address_ID = CST.CST_From_Address_ID and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID )  ) as location, CST.TIMESTAMP AS PS_ISSUE_DATE, COOL.COOL_LINE_ITEM_NO, P.PART_NUMBER, COOL.COOL_QTY, CSLIT_QTY_SHIPPED AS PULLED_QTY, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE FROM Sales_Order SO, Customer_Shipping_Table CST,Customer C, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE SO.SalesOrder_ID = CST.SalesOrder_ID and C.Customer_ID = SO.Customer_ID AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID '
		set @SQLString = N'INSERT INTO #PS_LINEITEMS_TABLE(salesOrder_Id, CustomerShipping_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, CST_IS_Drop_Shipment, 
							location, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY, MAX_SCHEDULE_DATE)
						 SELECT SO.SalesOrder_ID,CST.CustomerShipping_Id,Label,
							SalesOrder_No,C_Name, SO_Customer_PO_Number,
							CST_Status,Inv_No,CST_IS_Drop_Shipment, 
							isNull( (select top 1 L_Vendor_Name+''/''+a.City from Location l, Vendor v, Address a where l.Address_ID = CST.CST_Location_Address_Id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID ), (select top 1 L_Vendor_Name+''/''+a.City from Location l, Vendor v, Address a where l.Address_ID = CST.CST_From_Address_ID and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID )  ) as location, CST.TIMESTAMP AS PS_ISSUE_DATE, 
							COOL.COOL_LINE_ITEM_NO, P.PART_NUMBER, P.P_Marketing_Part_Number, COOL.COOL_QTY, XYZ.TOTAL_PULLED_QTY AS PULLED_QTY, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE 
							FROM Sales_Order SO, Customer_Shipping_Table CST,Customer C, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, PRODUCT P, CUSTOMER_OPEN_ORDER_LINEITEM COOL LEFT OUTER JOIN (SELECT SUM(CSLIT_QTY_SHIPPED) AS TOTAL_PULLED_QTY,LINEITEM_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CSLIT_STATUS !=''CANCELLED'' GROUP BY LINEITEM_ID) XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID WHERE SO.SalesOrder_ID = CST.SalesOrder_ID and C.Customer_ID = SO.Customer_ID AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID '
		SET @ParamDefinition = N'@Prm_Sales_Order_No varchar(50), @Prm_Customer_PO varchar(50), @Prm_Customer_ID int, @Prm_Product_ID INT, @Prm_Year varchar(15), @Prm_Address_ID int, @Prm_Status  varchar(50), @Prm_isSample int, @PRM_SHIPPED_AFTER VARCHAR(30)'

		IF @sales_order_no IS NOT NULL AND LTRIM ( RTRIM (@sales_order_no ) ) <> '' AND LTRIM ( RTRIM (@sales_order_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SalesOrder_No = @Prm_Sales_Order_No'
		END
		IF @customer_po_no IS NOT NULL AND LTRIM ( RTRIM (@customer_po_no ) ) <> '' AND LTRIM ( RTRIM (@customer_po_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SO_Customer_PO_Number = @Prm_Customer_PO'
		END
		IF @customer_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND C.Customer_ID = @Prm_Customer_ID'
		END
		IF @part_number_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND P.Product_ID = @Prm_Product_ID'
		END
		IF @location_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + '  AND ( CST_From_Address_ID = @Prm_Address_ID OR CST_Location_Address_ID = @Prm_Address_ID )';
		END

		IF @year IS NOT NULL AND LTRIM ( RTRIM (@year ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND YEAR( CST.Timestamp ) = @Prm_Year'
		END		
		IF ( @status = 'SHIPPED' AND @SHIPPED_AFTER IS NOT NULL AND LEN(LTRIM(RTRIM(@SHIPPED_AFTER))) > 0 )
		 BEGIN
			SET @SQLString = @SQLString + ' AND CST_Ship_Date > CAST(@PRM_SHIPPED_AFTER AS DATETIME)'
		 END
				
		SET @SQLString = @SQLString + ' AND CST_Status = @Prm_Status'
		SET @SQLString = @SQLString + ' AND SO_Is_Sample = @Prm_isSample'
		SET @SQLString = @SQLString + ' ORDER BY SalesOrder_No, CST.CustomerShipping_ID '

		execute sp_executesql @SQLString, @ParamDefinition, @Prm_Sales_Order_No = @sales_order_no, @Prm_Customer_PO = @customer_po_no, @Prm_Product_ID = @part_number_id, @Prm_Customer_ID = @customer_id, @Prm_Year = @year, @Prm_Address_ID = @location_id, @Prm_Status = @status, @Prm_isSample = @isSample, @PRM_SHIPPED_AFTER = @SHIPPED_AFTER
	END
	ELSE
	BEGIN
		
		--set @SQLString = N'declare psCursor CURSOR for SELECT SO.SalesOrder_Id,CustomerShipping_Id=-1,label=''null'',SalesOrder_No,C_Name,SO_Customer_PO_Number,CST_Status=''OPEN'',Inv_No='''', CST_IS_Drop_Shipment = -1, location = ''null'', NULL AS PS_ISSUE_DATE, COOL.COOL_LINE_ITEM_NO , P.PART_NUMBER, COOL.COOL_QTY, ISNULL(xyz.total,0) AS PULLED_QTY, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE FROM Sales_Order SO join Customer_Open_Order_LineItem COOL on SO.SalesOrder_ID = COOL.SalesOrder_ID join Customer C on SO.Customer_ID = C.Customer_ID JOIN PRODUCT P ON P.PRODUCT_ID = COOL.PRODUCT_ID left outer join (select SUM(CSLIT_Qty_Shipped) as total,LineItem_ID FROM Customer_Shipping_Line_Item_Table Where CSLIT_Status !=''CANCELLED'' group by LineItem_ID) xyz on COOL.LineItem_ID = xyz.LineItem_ID where  COOL_Status = ''APPROVED'' and ((xyz.total is null) or (xyz.total < COOL.COOL_Qty))'
		set @SQLString = N'INSERT INTO #PS_LINEITEMS_TABLE(salesOrder_Id, CustomerShipping_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, CST_IS_Drop_Shipment, 
							location, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY, MAX_SCHEDULE_DATE)
						 SELECT SO.SalesOrder_Id,CustomerShipping_Id=-1,label=''null'',
							SalesOrder_No,C_Name,SO_Customer_PO_Number,
							CST_Status=''OPEN'',Inv_No='''', CST_IS_Drop_Shipment = -1, 
							location = ''null'', NULL AS PS_ISSUE_DATE, 
							COOL.COOL_LINE_ITEM_NO , P.PART_NUMBER, P.P_Marketing_Part_Number, COOL.COOL_QTY, ISNULL(xyz.total,0) AS PULLED_QTY, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE 
						FROM Sales_Order SO join Customer_Open_Order_LineItem COOL on SO.SalesOrder_ID = COOL.SalesOrder_ID join Customer C on SO.Customer_ID = C.Customer_ID JOIN PRODUCT P ON P.PRODUCT_ID = COOL.PRODUCT_ID left outer join (select SUM(CSLIT_Qty_Shipped) as total,LineItem_ID FROM Customer_Shipping_Line_Item_Table Where CSLIT_Status !=''CANCELLED'' group by LineItem_ID) xyz on COOL.LineItem_ID = xyz.LineItem_ID where  COOL_Status = ''APPROVED'' and ((xyz.total is null) or (xyz.total < COOL.COOL_Qty))'
		SET @ParamDefinition = N'@Prm_Sales_Order_No varchar(50), @Prm_Customer_PO varchar(50), @Prm_Customer_ID int, @Prm_Product_ID INT, @Prm_Year varchar(15), @Prm_Address_ID int, @Prm_Status  varchar(50), @Prm_isSample int, @PRM_SCHEDULED_BEFORE VARCHAR(30), @PRM_SHIPPED_AFTER VARCHAR(30)'

		IF @sales_order_no IS NOT NULL AND LTRIM ( RTRIM (@sales_order_no ) ) <> '' AND LTRIM ( RTRIM (@sales_order_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SalesOrder_No = @Prm_Sales_Order_No'
		END
		IF @customer_po_no IS NOT NULL AND LTRIM ( RTRIM (@customer_po_no ) ) <> '' AND LTRIM ( RTRIM (@customer_po_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SO_Customer_PO_Number = @Prm_Customer_PO'
		END
		IF @customer_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND C.Customer_ID = @Prm_Customer_ID'
		END
		IF @part_number_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND P.Product_ID = @Prm_Product_ID'
		END
		/*IF @year IS NOT NULL AND LTRIM ( RTRIM ( @year ) ) <> ''
		BEGIN
			SET @SQLString = @SQLString + ' AND YEAR( Sales_Order.SO_Booking_Date ) = @Prm_Year'
		END*/
		IF ( @SCHEDULED_BEFORE IS NOT NULL AND LEN(LTRIM(RTRIM(@SCHEDULED_BEFORE))) > 0 )
		 BEGIN
			SET @SQLString = @SQLString + ' AND ISNULL(COOL_REVISED_DATE, COOL_SCHEDULE_DATE) < CAST(@PRM_SCHEDULED_BEFORE AS DATETIME)'
		 END
		SET @SQLString = @SQLString + ' AND SO_Is_Sample = @Prm_isSample'

		SET @SQLString = @SQLString + ' UNION ALL '
		
		SET @SQLString = @SQLString + ' SELECT SO.SalesOrder_ID,CST.CustomerShipping_Id,Label,SalesOrder_No,C_Name, SO_Customer_PO_Number,CST_Status,Inv_No,CST_IS_Drop_Shipment, isNull( (select top 1 L_Vendor_Name+''/''+a.City from Location l, Vendor v, Address a where l.Address_ID = CST.CST_Location_Address_Id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID ), (select top 1 L_Vendor_Name+''/''+a.City from Location l, Vendor v, Address a where l.Address_ID = CST.CST_From_Address_ID and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID ) ) as location, CST.TIMESTAMP AS PS_ISSUE_DATE, COOL.COOL_LINE_ITEM_NO , P.PART_NUMBER, P.P_Marketing_Part_Number, COOL.COOL_QTY, XYZ.TOTAL_PULLED_QTY AS PULLED_QTY, ISNULL(COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE) AS SCHEDULE_DATE FROM Sales_Order SO, Customer_Shipping_Table CST,Customer C, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, PRODUCT P, CUSTOMER_OPEN_ORDER_LINEITEM COOL LEFT OUTER JOIN (SELECT SUM(CSLIT_QTY_SHIPPED) AS TOTAL_PULLED_QTY,LINEITEM_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CSLIT_STATUS !=''CANCELLED'' GROUP BY LINEITEM_ID) XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID WHERE SO.SalesOrder_ID = CST.SalesOrder_ID and C.Customer_ID = SO.Customer_ID AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID AND P.PRODUCT_ID = COOL.PRODUCT_ID'
		
		IF @sales_order_no IS NOT NULL AND LTRIM ( RTRIM (@sales_order_no ) ) <> '' AND LTRIM ( RTRIM (@sales_order_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SalesOrder_No = @Prm_Sales_Order_No'
		END
		IF @customer_po_no IS NOT NULL AND LTRIM ( RTRIM (@customer_po_no ) ) <> '' AND LTRIM ( RTRIM (@customer_po_no ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND SO_Customer_PO_Number = @Prm_Customer_PO'
		END
		IF @customer_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND C.Customer_ID = @Prm_Customer_ID'
		END
		IF @location_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + '  AND ( CST_From_Address_ID = @Prm_Address_ID OR CST_Location_Address_ID = @Prm_Address_ID )';
		END
		IF @part_number_id  <> 0
		BEGIN
			SET @SQLString = @SQLString + ' AND P.Product_ID = @Prm_Product_ID'
		END
		IF @year IS NOT NULL AND LTRIM ( RTRIM (@year ) ) <> '0'
		BEGIN
			SET @SQLString = @SQLString + ' AND YEAR( CST.Timestamp ) = @Prm_Year'
		END
		IF ( @SHIPPED_AFTER IS NOT NULL AND LEN(LTRIM(RTRIM(@SHIPPED_AFTER))) > 0 )
		 BEGIN
			SET @SQLString = @SQLString + ' AND (CASE WHEN CST_STATUS = ''SHIPPED'' THEN CST_Ship_Date ELSE (CAST(@PRM_SHIPPED_AFTER AS DATETIME)+1) END) > CAST(@PRM_SHIPPED_AFTER AS DATETIME)'
		 END
		SET @SQLString = @SQLString + ' AND SO_Is_Sample = @Prm_isSample' 
		SET @SQLString = @SQLString + ' ORDER BY SalesOrder_No '		
		
		execute sp_executesql @SQLString, @ParamDefinition, @Prm_Sales_Order_No = @sales_order_no, @Prm_Customer_PO = @customer_po_no, @Prm_Product_ID = @part_number_id, @Prm_Customer_ID = @customer_id, @Prm_Year = @year, @Prm_Address_ID = @location_id, @Prm_Status = @status, @Prm_isSample = @isSample, @PRM_SCHEDULED_BEFORE = @SCHEDULED_BEFORE, @PRM_SHIPPED_AFTER = @SHIPPED_AFTER

	END
	--print '***' + @SQLString + '***'	
	IF ( @IS_LINEITEM_WISE = 0 )
	BEGIN
		INSERT INTO @final_results(CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY)
			SELECT CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, NULL/*MAX_SCHEDULE_DATE*/, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY 
				FROM #PS_LINEITEMS_TABLE where cst_status != 'OPEN' ORDER BY PS_ISSUE_DATE DESC, SALESORDER_NO DESC
	
		INSERT INTO @final_results(CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY)
			SELECT CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER,MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY FROM #PS_LINEITEMS_TABLE where cst_status = 'OPEN' AND MAX_SCHEDULE_DATE IS NOT NULL ORDER BY MAX_SCHEDULE_DATE ASC, SALESORDER_NO ASC

		INSERT INTO @final_results(CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY)
			SELECT CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE,
							COOL_LINE_ITEM_NO, PART_NUMBER,MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY FROM #PS_LINEITEMS_TABLE where cst_status = 'OPEN' AND MAX_SCHEDULE_DATE IS NULL ORDER BY SALESORDER_NO
		
	END
	ELSE --SALES ORDER WISE
	BEGIN
		INSERT INTO @final_results(CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, /*MAX_SCHEDULE_DATE,*/ PS_ISSUE_DATE
							--COOL_LINE_ITEM_NO, PART_NUMBER, COOL_QTY, PULLED_QTY
							)
			SELECT CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, /*MAX_SCHEDULE_DATE,*/ PS_ISSUE_DATE
							--COOL_LINE_ITEM_NO, PART_NUMBER, COOL_QTY, PULLED_QTY 
				FROM #PS_LINEITEMS_TABLE 
				where cst_status != 'OPEN' 
				GROUP BY CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, /*MAX_SCHEDULE_DATE,*/ PS_ISSUE_DATE
				ORDER BY PS_ISSUE_DATE DESC, SALESORDER_NO DESC
	
		INSERT INTO @final_results(CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE
							--COOL_LINE_ITEM_NO, PART_NUMBER, COOL_QTY, PULLED_QTY
							)
			SELECT CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX(MAX_SCHEDULE_DATE), PS_ISSUE_DATE
							--COOL_LINE_ITEM_NO, PART_NUMBER, COOL_QTY, PULLED_QTY 
				FROM #PS_LINEITEMS_TABLE 
				where cst_status = 'OPEN' AND MAX_SCHEDULE_DATE IS NOT NULL 
				GROUP BY CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, /*MAX_SCHEDULE_DATE,*/PS_ISSUE_DATE
				ORDER BY MAX(MAX_SCHEDULE_DATE) ASC, SALESORDER_NO ASC

		INSERT INTO @final_results(CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE
							--COOL_LINE_ITEM_NO, PART_NUMBER, COOL_QTY, PULLED_QTY
							)
			SELECT CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, MAX(MAX_SCHEDULE_DATE), PS_ISSUE_DATE
							--COOL_LINE_ITEM_NO, PART_NUMBER, COOL_QTY, PULLED_QTY 
				FROM #PS_LINEITEMS_TABLE 
				where cst_status = 'OPEN' AND MAX_SCHEDULE_DATE IS NULL 
				GROUP BY CustomerShipping_Id, salesOrder_Id, Label, 
							SalesOrder_No, C_Name, SO_Customer_PO_number, 
							CST_Status, Inv_No, location,
							CST_IS_Drop_Shipment, /*MAX_SCHEDULE_DATE,*/PS_ISSUE_DATE
				ORDER BY SALESORDER_NO
	END

	DECLARE @PS_CURSOR CURSOR
	DECLARE @CUSTOMERSHIPPING_ID INT

	SET @PS_CURSOR = CURSOR FOR SELECT DISTINCT CustomerShipping_Id FROM @final_results WHERE CST_Status = 'ISSUED'
	OPEN @PS_CURSOR 
	FETCH NEXT FROM @PS_CURSOR INTO @CUSTOMERSHIPPING_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN

		
		DECLARE @IS_READY_FOR_CONFIRM INT
		EXEC SP_IS_PACKING_SLIP_READY_FOR_CONFIRM @CUSTOMERSHIPPING_ID, @IS_READY_FOR_CONFIRM OUT

		IF (@IS_READY_FOR_CONFIRM = 0)
		BEGIN
			UPDATE @final_results SET IS_READY_FOR_CONFIRM = 0 WHERE CustomerShipping_Id = @CUSTOMERSHIPPING_ID
		END
		
		SET @CUSTOMERSHIPPING_ID = 0

		FETCH NEXT FROM @PS_CURSOR INTO @CUSTOMERSHIPPING_ID
	END
	CLOSE @PS_CURSOR
	DEALLOCATE @PS_CURSOR

	SELECT CustomerShipping_Id, salesOrder_Id, Label, SalesOrder_No, C_Name, SO_Customer_PO_number, CST_Status, Inv_No, location, CST_IS_Drop_Shipment, MAX_SCHEDULE_DATE, PS_ISSUE_DATE, COOL_LINE_ITEM_NO, PART_NUMBER, MRKT_PART_NUMBER, COOL_QTY, PULLED_QTY, IS_READY_FOR_CONFIRM FROM @final_results
	RETURN 
END

